package com.enlorenz.common.excel.old;

import java.io.FileInputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.util.CellRangeAddress;

import com.enlorenz.common.util.DataDetUtils;

/**
 * Excel模板类
 * @author lcb
 */
public class ExcelTemplate {
	//更列表模板
	private List<RowWrapper> rootList;
	private Integer [] columsWidth;
	
	public ExcelTemplate(String filePath) throws Exception{
		rootList=parseExcel2Template(filePath);
	}
	/**
	 * 输出数据
	 * @param dataMap
	 * @param os
	 * @throws Exception 
	 */
	public void ExportExcel(Map dataMap,OutputStream os) throws Exception{
		HSSFWorkbook output=new HSSFWorkbook();
		HSSFSheet outputSheet=output.createSheet("sheet1");
		int rowNum=0;
		for(RowWrapper rw:rootList){
			rowNum=itRw(rw, outputSheet, output, rowNum, dataMap,null);
		}
		
		for(int i=0;i<columsWidth.length;i++){
			outputSheet.setColumnWidth(i, columsWidth[i]);
		}
		
		output.write(os);
	}
	
	
	private int itRw(RowWrapper rw,HSSFSheet outputSheet,HSSFWorkbook output,int rowNum,Map values,String what){
		int index2=0;
		
		String all="";
		HSSFRow oupoutRow=outputSheet.createRow(rowNum);
		oupoutRow.setHeight((short)rw.getRowHeight());
		
		List<CellRangeAddress> rList=rw.rList;
		for(CellRangeAddress r:rList){
			CellRangeAddress cra=new CellRangeAddress(rowNum,rowNum,r.getFirstColumn(),r.getLastColumn());
			outputSheet.addMergedRegion(cra);
		}
		String allCell="Start【";
		for(HSSFCell cell:rw.columns){
			HSSFCell ouputCell=oupoutRow.createCell(index2);
			HSSFCellStyle ouputStyle=output.createCellStyle();
			ouputStyle.cloneStyleFrom(cell.getCellStyle());
			ouputStyle.setWrapText(true);
			ouputCell.setCellStyle(ouputStyle);
			String cellValue=cell.toString();
			
			allCell+=cellValue;
			if(-1!=cellValue.indexOf("${")){
					String [] arr=cell.toString().split("\\$\\{|\\}");
					StringBuilder strs=new StringBuilder();
					for(int i=0;i<arr.length;i++){
						String val=arr[i];
						if(i%2 == 0){
							strs.append(val);
						}else{
							String [] valArrs=val.split("\\.");
							Map tmpMap=values;
							String strVals="";
							for(int j=0;j<valArrs.length;j++){
								String valArrKey=valArrs[j];
								if(j == valArrs.length-1){
									strVals=(String)tmpMap.get(valArrKey);
								}else{
									tmpMap=(Map)tmpMap.get(valArrKey);
								}
							}
							
							strs.append(strVals);
						}
					}
					
					cellValue=strs.toString();
			}
			
			ouputCell.setCellValue(new HSSFRichTextString(cellValue));
			
			all+=cellValue;
			index2++;
		}
		//System.out.println(rw.getIteratorKey()+":"+rowNum+":"+all+":"+rw.childWrapper);
		allCell+="】";
		System.out.println(rowNum+"::"+rw.columns.size()+":"+rw.getIteratorKey()+":"+allCell);
		
		if (null == rw.getIteratorKey()) {
			
		} else {
			String listKey = rw.getIteratorKey();
			List<Map> list=null;
			if(-1!=listKey.indexOf(".")){
				String [] arr=listKey.split("\\.");
				String key1=arr[0];
				String key2=arr[1];
				Map map=(Map)values.get(key1);
				list= (List)map.get(key2);
			}else{
				list= (List) values.get(listKey);
			}
			
			String simpleKey=listKey.substring(listKey.length()-4,listKey.length()-2);
			
			if (!DataDetUtils.isCollectionEmpty(list)) {
				for (Map object : list) {
					values.put(simpleKey, object);
					for (RowWrapper childRw : rw.childWrapper) {
						rowNum = itRw(childRw, outputSheet, output, rowNum, values,
								simpleKey);
						
					}
				}

				return rowNum;
			}

		}
		
		

		rowNum++; 
		return rowNum;
	}
	/**
	 * 将exel解析成模板
	 * @param filePath
	 * @return
	 * @throws Exception
	 */
	public List<RowWrapper> parseExcel2Template(String filePath) throws Exception{
		FileInputStream fis=null;
		try{
			fis=new FileInputStream(filePath);
			return parseExcel2Template(fis);
		}catch (Exception e) {
			throw e;
		}finally{
			if(null!=fis){
				fis.close();
			}
		}
	}
	
	public List<RowWrapper> parseExcel2Template(FileInputStream inputStream) throws Exception{
		//用模板文件构造poi   
		POIFSFileSystem fs = new POIFSFileSystem(inputStream);
		//创建模板工作表   
		HSSFWorkbook templatewb = new HSSFWorkbook(fs);
		//直接取模板第一个sheet对象   
		HSSFSheet templateSheet = templatewb.getSheetAt(0);
		/*int mIndex=templateSheet.getNumMergedRegions();
		System.out.println(mIndex);
		for(int i=0;i<mIndex;i++){
			CellRangeAddress cra=templateSheet.getMergedRegion(i);
			System.out.println(cra.getFirstColumn()+":"+cra.getLastColumn()
					+":"+cra.getFirstRow()+":"+cra.getLastRow());
		}*/
		//获取总行数
		int rows=templateSheet.getPhysicalNumberOfRows();
		List<RowWrapper> rootList=new ArrayList<RowWrapper>();
		//循环所有的节点
		iteratorAllNode(0, rows, templateSheet,rootList,"");
		return rootList;
	}
	
	private List<CellRangeAddress> getRangeList(HSSFSheet templateSheet,int row){
		List<CellRangeAddress> list=new ArrayList<CellRangeAddress>();
		int mIndex=templateSheet.getNumMergedRegions();
		for(int i=0;i<mIndex;i++){
			CellRangeAddress cra=templateSheet.getMergedRegion(i);
			
			if(cra.getFirstRow()<=row && row<=cra.getLastRow()){
				list.add(cra);
			}
		}

		return list;
	}
	
	/**
	 * 
	 * @param startRow	开始行数
	 * @param endRow	结束行数
	 * @param templateSheet	Sheet
	 * @param childList	
	 * @param key
	 * @return
	 */
	private int iteratorAllNode(int startRow,int endRow,HSSFSheet templateSheet,List<RowWrapper> childList,String key){
		//循环行
		for(int i=startRow;i<endRow;i++){
			HSSFRow templateRow = templateSheet.getRow(i);
			int columns = templateRow.getPhysicalNumberOfCells();
			
			RowWrapper rw=new RowWrapper();
			
			//获取行高
			int height=templateRow.getHeight();
			rw.setRowHeight(height);
			List<CellRangeAddress> regList=getRangeList(templateSheet, i);
			rw.setRList(regList);
			
			
			
			//如果列的宽度信息为空，则创建一个
			if(null == columsWidth){
				columsWidth=new Integer[columns];
				for(int j=0;j<columns;j++){
					columsWidth[j]=templateSheet.getColumnWidth(j);
				}
			}
			
			for(int j=0;j<columns;j++){
				HSSFCell cell=templateRow.getCell(j);
				String cellContent=cell.toString();
				//如果是循环开始
				if(isIteratorStart(cellContent)){
					String ikey=analyzeIteratorKey(cellContent);
					//设置循环的关键字
					rw.setIteratorKey(ikey);
					i=iteratorAllNode(i+1, endRow, templateSheet,rw.childWrapper,cell.toString());
					break;
				//如果是循环结束
				}else if(isIteratorEnd(cellContent)){
					return i;
				}
				rw.columns.add(cell);
			}
			
			childList.add(rw);
		}
		return endRow;
	}
	/**
	 * 是否是循环的开始
	 * @param content
	 * @return
	 */
	private static boolean isIteratorStart(String content){
		return -1!=content.indexOf("循环开始");
	}
	
	/**
	 * 分解循环的标志
	 * @param content
	 * @return
	 */
	private static String analyzeIteratorKey(String content){
		String [] arrs=content.split(":");
		return arrs[1].substring(0,arrs[1].length()-1);
	}
	
	/**
	 * 是否是循环结束
	 * @param content
	 * @return
	 */
	private static boolean isIteratorEnd(String content){
		return -1!=content.indexOf("循环结束");
	}
	
	private static class RowWrapper{
		//行的高度
		private int rowHeight;
		//
		private List<CellRangeAddress> rList;
		//列数据
		private List<HSSFCell> columns=new ArrayList<HSSFCell>();
		//子数据
		private List<RowWrapper> childWrapper=new ArrayList<RowWrapper>();
		//循环的标志
		private String iteratorKey;
		
		public List<HSSFCell> getColumns() {
			return columns;
		}
		public void setColumns(List<HSSFCell> columns) {
			this.columns = columns;
		}
		public String getIteratorKey() {
			return iteratorKey;
		}
		public void setIteratorKey(String iteratorKey) {
			this.iteratorKey = iteratorKey;
		}
		public int getRowHeight() {
			return rowHeight;
		}
		public void setRowHeight(int rowHeight) {
			this.rowHeight = rowHeight;
		}
		public List<CellRangeAddress> getRList() {
			return rList;
		}
		public void setRList(List<CellRangeAddress> list) {
			rList = list;
		}
		
	}
	
	public static String trans(int type) {
		if(type == HSSFCell.CELL_TYPE_FORMULA){
			return "CELL_TYPE_FORMULA";
		}else if(type == HSSFCell.CELL_TYPE_NUMERIC){
			return "CELL_TYPE_NUMERIC";
		}else if(type == HSSFCell.CELL_TYPE_STRING){
			return "CELL_TYPE_STRING";
		}
		return String.valueOf(type);
	}
	

	
	public static void main(String[] args) throws Exception {
		Map values=new HashMap();
		 List<Map> map=new ArrayList();
		 
		 Map typ1=new HashMap();
		 List<Map> list1=new ArrayList();
		 
		 Map ll1=new HashMap();
		 ll1.put("名称", "名称1");
		 ll1.put("品牌", "品牌1");
		 ll1.put("型号", "型号1");
		 ll1.put("性能", "性能1");
		 list1.add(ll1);
		 ll1=new HashMap();
		 ll1.put("名称", "名称2");
		 ll1.put("品牌", "品牌2");
		 ll1.put("型号", "型号2");
		 ll1.put("性能", "性能2");
		 list1.add(ll1);
		 ll1=new HashMap();
		 ll1.put("名称", "名称3");
		 ll1.put("品牌", "品牌3");
		 ll1.put("型号", "型号3");
		 ll1.put("性能", "性能3");
		 list1.add(ll1);
		 
		 typ1.put("详单列表", list1);
		 typ1.put("类列名", "xo1");
		 map.add(typ1);
		 Map typ2=new HashMap();
		 typ2.put("详单列表", list1);
		 typ2.put("类列名", "xo2");
		 
		 map.add(typ2);
		 Map typ3=new HashMap();
		 typ3.put("详单列表", list1);
		 typ3.put("类列名", "xo3");
		 
		 map.add(typ3);
		 
		 
		 values.put("类别列表", map);
		 values.put("日期", "1922-09-83");
		 values.put("联系人", "Kima");
		 values.put("电话", "18609232344");
		 values.put("传真", "512344");
		 values.put("邮箱", "11233423@qq.com");
		 values.put("QQ", "1233423");
		new ExcelTemplate("c:\\报价清单.xls").ExportExcel(values, null);
	}
	
	
}
